package tsli.acm.dao;

import java.util.List;
import java.util.Map;

import tsli.acm.database.Database;
import tsli.acm.model.SFCAgentStructure;

public class SFCAgentStructureDAO {
	
	private Database db;

	 public SFCAgentStructureDAO(Database db) {
		 this.db = db;
	 }
	 
	 /**
	  * Finding recruiters that recruiting people who make profit to company
	  * @param paDate date
	  * @return recruiters
	  */
	 public String[] findRecruiterIdBetweenOneToSix(String paDate) {
//	    	String vaSql = "SELECT DISTINCT pe_no_rcrut FROM ( "
//	    			+ "SELECT a.*, sum(b.sum_fyp) FROM (SELECT pe_no_rcrut, pe_no, nm_title_thai, nm_thai, surnm_thai "
//	    			+ "FROM tsli_sfc_agent_structure " 
//	    			+ "WHERE DTY_CD IN ('S10240205','S10240206') AND (fst_enco_mms BETWEEN 1 AND 6) " 
//	    			+ "AND clos_ym = ?) a "
//	    			+ "JOIN " 
//	    			+ "(SELECT clos_ym, pe_no_col, po_no, SUM(fyp) AS sum_fyp FROM tsli_sfc_commission_positive "
//	    			+ "WHERE (? - CLOS_YM) between 0 and 5 "
//	    			+ "GROUP BY clos_ym, pe_no_col, po_no) b "
//	    			+ "ON a.pe_no = b.pe_no_col "
//	    			+ "GROUP BY a.pe_no_rcrut, a.pe_no, a.nm_title_thai, a.nm_thai, a.surnm_thai "
//	    			+ "ORDER BY a.pe_no_rcrut, a.pe_no) ORDER BY pe_no_rcrut";
		 	String vaSql = "SELECT DISTINCT pe_no_rcrut FROM ( "
				 + "SELECT a.*, sum(b.sum_fyp) AS sum_fyp, b.clos_ym FROM ( "
				 + "SELECT pe_no_rcrut, pe_no, nm_title_thai, nm_thai, surnm_thai, fst_enco_mms "
				 + "FROM tsli_sfc_agent_structure "
				 + "WHERE DTY_CD IN ('S10240205','S10240206') AND (fst_enco_mms BETWEEN 1 AND 6) "
				 + "AND clos_ym = ?) a "
				 + "JOIN ( "
				 + "SELECT clos_ym, pe_no_col, po_no, SUM(fyp) AS sum_fyp FROM tsli_sfc_commission_positive "
				 + "WHERE MONTHS_BETWEEN (TO_DATE(?, 'YYYYMM'), TO_DATE(CLOS_YM, 'YYYYMM')) between 0 and 5 " 
				 + "GROUP BY clos_ym, pe_no_col, po_no) b "
				 + "ON a.pe_no = b.pe_no_col " 
				 + "GROUP BY a.pe_no_rcrut, a.pe_no, a.nm_title_thai, a.nm_thai, a.surnm_thai, a.fst_enco_mms, b.clos_ym " 
				 + "ORDER BY a.pe_no_rcrut, a.pe_no) ORDER BY pe_no_rcrut";
	    	List<Map<String, Object>> voResult = db.queryList(vaSql, paDate, paDate);
	    	String[] vaResult = null;
	    	if (voResult != null) {
	    		vaResult = new String[voResult.size()];
	    		for (int i = 0; i < voResult.size(); i++) {
	    			Map<String, Object> voTmp = voResult.get(i);
	    			vaResult[i] = (String) voTmp.get("PE_NO_RCRUT");
	    		}
	            return vaResult;
	        } else {
	            return null;
	        }
	 }
	 
	 public String[] findNameById(String paPeNo) {
		 String vaSql = "SELECT nm_title_thai, nm_thai, surnm_thai FROM tsli_sfc_agent_structure WHERE pe_no = ?";
		 Map<String, Object> result = db.querySingle(vaSql, paPeNo);
		 String[] vaResult = new String[3];
		 if (result != null) {
			 vaResult[0] = (String) result.get("NM_TITLE_THAI");
			 vaResult[1] = (String) result.get("NM_THAI");
			 vaResult[2] = (String) result.get("SURNM_THAI");
			 return vaResult;
		 } else {
			 vaResult[0] = "";
			 vaResult[1] = "";
			 vaResult[2] = "";
			 return vaResult;
	     }
	 }
	 
	 public String[] findNewEmployee(String paRecruiterId, String paDate) {
//		 String vaSql = "SELECT DISTINCT pe_no FROM ( "
//				 + "SELECT * FROM (SELECT pe_no_rcrut, pe_no, nm_title_thai, nm_thai, surnm_thai "
//				 + "FROM tsli_sfc_agent_structure " 
//				 + "WHERE DTY_CD IN ('S10240205','S10240206') AND (fst_enco_mms BETWEEN 1 AND 6) " 
//				 + "AND clos_ym = ?) a "
//				 + "JOIN " 
//				 + "(SELECT clos_ym, pe_no_col, po_no, SUM(fyp) AS sum_fyp FROM tsli_sfc_commission_positive "
//				 + "WHERE (? - CLOS_YM) between 0 and 5 "
//				 + "GROUP BY clos_ym, pe_no_col, po_no) b "
//				 + "ON a.pe_no = b.pe_no_col "
//				 + "ORDER BY a.pe_no_rcrut, a.pe_no "
//				 + ") WHERE pe_no_rcrut = ? ORDER BY pe_no"; 
		 String vaSql = "SELECT DISTINCT pe_no FROM ( "
				 + "SELECT a.*, sum(b.sum_fyp) AS sum_fyp, b.clos_ym FROM ( "
				 + "SELECT pe_no_rcrut, pe_no, nm_title_thai, nm_thai, surnm_thai, fst_enco_mms "
				 + "FROM tsli_sfc_agent_structure "
				 + "WHERE DTY_CD IN ('S10240205','S10240206') AND (fst_enco_mms BETWEEN 1 AND 6) "
				 + "AND clos_ym = ?) a "
				 + "JOIN ( "
				 + "SELECT clos_ym, pe_no_col, po_no, SUM(fyp) AS sum_fyp FROM tsli_sfc_commission_positive "
				 + "WHERE MONTHS_BETWEEN (TO_DATE(?, 'YYYYMM'), TO_DATE(CLOS_YM, 'YYYYMM')) between 0 and 5 " 
				 + "GROUP BY clos_ym, pe_no_col, po_no) b "
				 + "ON a.pe_no = b.pe_no_col " 
				 + "GROUP BY a.pe_no_rcrut, a.pe_no, a.nm_title_thai, a.nm_thai, a.surnm_thai, a.fst_enco_mms, b.clos_ym " 
				 + "ORDER BY a.pe_no_rcrut, a.pe_no) WHERE pe_no_rcrut = ? ORDER BY pe_no";
		 List<Map<String, Object>> voResult = db.queryList(vaSql, paDate, paDate, paRecruiterId);
		 String[] vaResult = null;
	    if (voResult != null) {
	    	vaResult = new String[voResult.size()];
	    	for (int i = 0; i < voResult.size(); i++) {
	    		Map<String, Object> voTmp = voResult.get(i);
	    		vaResult[i] = (String) voTmp.get("PE_NO");
	    	}
	        return vaResult;
	    } else {
	        return null;
	    }
	 }
	 
	 public void insertSFCAgentStructure(SFCAgentStructure poSFCAgentStructure) {
	    	String vaSql = "INSERT INTO TSLI_SFC_AGENT_STRUCTURE" + " (PE_NO,CLOS_YM,NM_TITLE_THAI,NM_THAI,SURNM_THAI,DTY_CD,"
	    			+ "FST_ENCO_YMD,RTM_YMD,FST_ENCO_MMS,PE_NO_RCRUT_BM,PE_NO_RCRUT_MM,PE_NO_RCRUT,PE_NO_BM,PE_NO_MM,"
	    			+ "PE_NO_SM,CHNL_TP_CD,ORGNO_BRN,ORGNO_OFFC,TEAM_CD,PE_NO_LCNS_REF,LCNS_RNWL_YMD,LCNS_EXP_YMD,"
	    			+ "CARPARK_YN,ZONE_CD,TIER1,TIER2,TIER3,AGENT_STS_CD,ID_CARD) " 
	        		+ "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? , ? , ?)";
	        try {
	            db.insert(vaSql 
	            		,poSFCAgentStructure.getVnPeNo()
	            		,poSFCAgentStructure.getVaClosYm()
	            		,poSFCAgentStructure.getVaNmTitleThai()
	            		,poSFCAgentStructure.getVaNmThai()
	            		,poSFCAgentStructure.getVaSurnmThai()
	            		,poSFCAgentStructure.getVaDtyCd()
	            		,poSFCAgentStructure.getVaFstEncoYmd()
	            		,poSFCAgentStructure.getVaRtmYmd()
	            		,poSFCAgentStructure.getVaFstEncoMms()
	            		,poSFCAgentStructure.getVaPeNoRcrutBm()
	            		,poSFCAgentStructure.getVaPeNoRcrutMm()
	            		,poSFCAgentStructure.getVaPeNoRcrut()
	            		,poSFCAgentStructure.getVaPeNoBm()
	            		,poSFCAgentStructure.getVaPeNoMm()
	            		,poSFCAgentStructure.getVaPeNoSm()
	            		,poSFCAgentStructure.getVaChnlTpCd()
	            		,poSFCAgentStructure.getVaOrgnoBrn()
	            		,poSFCAgentStructure.getVaOrgnoOffc()
	            		,poSFCAgentStructure.getVaTeamCd()
	            		,poSFCAgentStructure.getVaPeNoLcnsRef()
	            		,poSFCAgentStructure.getVaLcnsRnwlYmd()
	            		,poSFCAgentStructure.getVaLcnsExpYmd()
	            		,poSFCAgentStructure.getVaCarparkYn()
	            		,poSFCAgentStructure.getVaZoneCd()
	            		,poSFCAgentStructure.getVaTier1()
	            		,poSFCAgentStructure.getVaTier2()
	            		,poSFCAgentStructure.getVaTier3()
	            		,poSFCAgentStructure.getVaAgentStsCd()
	            		,poSFCAgentStructure.getVnIdCard());
	        } catch (Exception e) {
	        	e.printStackTrace();
	        	throw new RuntimeException(e);
	        }
	 }
	 
	 
	 public List<Map<String, Object>> findSFCAgent(String paClosYm) {

		 
			String vaSql = "SELECT DISTINCT agent_structure.PE_NO,agent_structure.NM_TITLE_THAI,agent_structure.NM_THAI,agent_structure.SURNM_THAI"
					+ "				 FROM tsli_sfc_agent_structure  agent_structure"
					+ "					              LEFT  JOIN tsli_sfc_commission_positive  commission_positive "
					+ "              ON commission_positive.PE_NO_COL = agent_structure.PE_NO "
					+ "				 WHERE  ( agent_structure.fst_enco_mms BETWEEN 1 AND 12 ) "
					+ "				 AND commission_positive.clos_ym = ?"
					+ "				 AND agent_structure.dty_cd  in ('S10240205','S10240206')";

			List<Map<String, Object>> result = db.queryList(vaSql,paClosYm);
			if (result != null) {
				return result;
			} else {
				return null;
			}
		}
	 
	 public List<Map<String, Object>> getAllAgentStructure() {
			String vaSql = "SELECT * "
					+ "FROM TSLI_SFC_AGENT_STRUCTURE";
			List<Map<String, Object>> result = db.queryList(vaSql);
	        
	        if (result != null && result.size() > 0) {
	            return result;
	        } else {
	            return null;
	        }
		}
	 
	 public List<Map<String, Object>> getAllHeadMmOfSC() {
			String vaSql = "select PE_NO,NM_TITLE_THAI || ' '||NM_THAI||' '||SURNM_THAI AS MM_NAME FROM tsli_sfc_agent_structure"
					+ " WHERE PE_NO = PE_NO_MM AND  DTY_CD = 'S10240202'";
			List<Map<String, Object>> result = db.queryList(vaSql);
	        
	        if (result != null && result.size() > 0) {
	            return result;
	        } else {
	            return null;
	        }
	}
	 public List<Map<String, Object>> getHeadMmOfSfcCaseClosYm(String paClosYm) {
			String vaSql = "select PE_NO,NM_TITLE_THAI || ' '||NM_THAI||' '||SURNM_THAI AS MM_NAME FROM tsli_sfc_agent_structure"
					+ " WHERE PE_NO = PE_NO_MM AND  DTY_CD = 'S10240202' AND CLOS_YM = ?";
			List<Map<String, Object>> result = db.queryList(vaSql, paClosYm);
	        
	        if (result != null && result.size() > 0) {
	            return result;
	        } else {
	            return null;
	        }
	}
	 public List<Map<String, Object>> getHeadMmOfSfcCaseMM(String paPeNoMm) {
			String vaSql = "select PE_NO,NM_TITLE_THAI || ' '||NM_THAI||' '||SURNM_THAI AS MM_NAME FROM tsli_sfc_agent_structure"
					+ " WHERE PE_NO = PE_NO_MM AND  DTY_CD = 'S10240202' AND PE_NO_MM = ? ";
			List<Map<String, Object>> result = db.queryList(vaSql, paPeNoMm);
	        
	        if (result != null && result.size() > 0) {
	            return result;
	        } else {
	            return null;
	        }
	}
	 public List<Map<String, Object>> getHeadMmOfSfcAndCase(String paPeNoMm, String paClosYm) {
			String vaSql = "select PE_NO,NM_TITLE_THAI || ' '||NM_THAI||' '||SURNM_THAI AS MM_NAME FROM tsli_sfc_agent_structure"
					+ " WHERE PE_NO = PE_NO_MM AND  DTY_CD = 'S10240202' AND PE_NO_MM = ? AND CLOS_YM = ?";
			List<Map<String, Object>> result = db.queryList(vaSql, paPeNoMm, paClosYm);
	        
	        if (result != null && result.size() > 0) {
	            return result;
	        } else {
	            return null;
	        }
	}
	 
	 public List<Map<String, Object>> getDetailPC(String paPeNoMm, String paClosYm) {
			String vaSql = " SELECT commission_positive.CLOS_YM, commission_positive.PE_NO_COL,commission_positive.PO_NO,commission_positive.AFYP,"
					+ " commission_positive.ARYP2,sum( commission_positive.AFYP+commission_positive.ARYP2) AS PC "
					+ " FROM tsli_sfc_commission_positive  commission_positive "
					+ " WHERE  commission_positive.PE_NO_MM = ? AND clos_ym = ? "
					+ " GROUP BY commission_positive.PE_NO_MM, commission_positive.PE_NO_COL, commission_positive.PO_NO ,"
					+ " commission_positive.CLOS_YM, commission_positive.AFYP, commission_positive.ARYP2 ";
			List<Map<String, Object>> result = db.queryList(vaSql, paPeNoMm, paClosYm);
	        
	        if (result != null && result.size() > 0) {
	            return result;
	        } else {
	            return null;
	        }
	}
	 
	 public List<Map<String, Object>> getSFCAgentData(String paPeNo, String paClosYm) {
		 
		 String condition = "";
		 if(!paPeNo.equals("")){
			 condition = "PE_NO = "+paPeNo+" AND";
		 }
		 
			String vaSql = "SELECT DISTINCT PE_NO,CLOS_YM,NM_TITLE_THAI,NM_THAI,SURNM_THAI,FST_ENCO_MMS,DTY_CD"
					+ " FROM TSLI_SFC_AGENT_STRUCTURE"
					+ " WHERE "+condition+" CLOS_YM = ? ";
			List<Map<String, Object>> result = db.queryList(vaSql,paClosYm);
  
	        if (result != null && result.size() > 0) {
	            return result;
	        } else {
	            return null;
	        }
	}
	 
	 
	 public String updateSFCAgentData(SFCAgentStructure voSFCAgentStructure){
		 
		 String result = "";
		 
		 try {
			 String vaSql = "UPDATE TSLI_SFC_AGENT_STRUCTURE"
			 		+ " SET NM_TITLE_THAI = ?,NM_THAI = ?,SURNM_THAI = ?"
			 		+ " WHERE PE_NO = ? AND CLOS_YM = ?";
			 
			 db.update(vaSql,voSFCAgentStructure.getVaNmTitleThai(),
					 voSFCAgentStructure.getVaNmThai(),
					 voSFCAgentStructure.getVaSurnmThai(),
					 voSFCAgentStructure.getVnPeNo(),
					 voSFCAgentStructure.getVaClosYm());
			 result = "success";
		} catch (Exception e) {
			e.printStackTrace();
			result = "fail";
		}
		 
		 return result;
	 }
	 
	 
	public String deleteSFCAgentData(String paPeNo, String paClosYm){
			 
			 String result = "";
			 
			 try {
				 String vaSql = "DELETE FROM TSLI_SFC_AGENT_STRUCTURE "
			 		+ " WHERE PE_NO = ? AND CLOS_YM = ?";
	
				 db.update(vaSql,paPeNo,paClosYm);
				 result = "success";
			} catch (Exception e) {
				e.printStackTrace();
				result = "fail";
			}
			 
			 return result;
		 }

	public String checkNewPeNoMM( String paPeNoMm, String paClosYm ) {
		String vaSql = "SELECT FST_ENCO_MMS "
				+ " FROM TSLI_SFC_AGENT_STRUCTURE "
				+ " WHERE PE_NO_MM = ? AND CLOS_YM = ? AND DTY_CD = 'S10240202' ";
		Map<String, Object> result = db.querySingle(vaSql, paPeNoMm, paClosYm);
		String dataResult = "";
		try {
			dataResult = result.get("FST_ENCO_MMS").toString();
		} catch (Exception e) {
			dataResult = null;
		}		
		return dataResult;
	}
}
